from fastapi import APIRouter, Depends, HTTPException, Query, Request,Cookie
from fastapi.responses import HTMLResponse, RedirectResponse
from fastapi.templating import Jinja2Templates
from sqlalchemy.orm import Session
from database import get_db
from models import EFAlarm, Device, ElectFence,RegionRiskLevel,HourlyRiskDistribution
from datetime import datetime, timedelta
import json
from core.logger import app_logger as logger
from utils import get_mqtt_client
from paho.mqtt import client as mqtt_client
import time
from urllib.parse import unquote 
from typing import Optional
from templates import templates
from utils import validate_session
import pandas as pd
router = APIRouter()

# MQTT 订阅回调函数
def on_efence_message(client, userdata, msg):
    try:
        payload = json.loads(msg.payload.decode())
        logger.info(f"收到电子围栏报警消息: {payload}")
        
        db = next(get_db())
        device_id = payload.get("id")
        cmd = payload.get("cmd")
        
        # 查询电子围栏设备的位置信息
        elect_fence = db.query(ElectFence).filter(
            ElectFence.FenceName == device_id,
            ElectFence.DeadTime == None
        ).first()
        
        fence_location = elect_fence.FenceDesc if elect_fence else "未知位置"
        
        if cmd == "A":  # 报警开始
            new_alarm = EFAlarm(
                FenceName=device_id,
                FenceLocation=fence_location,  # 新增位置信息
                StartTime=datetime.utcnow(),
                FenceState="报警中",
                CreateTime=datetime.utcnow()
            )
            db.add(new_alarm)
            db.commit()
            logger.info(f"创建新的电子围栏报警: {device_id}, 位置: {fence_location}")
            
        elif cmd == "a":  # 报警结束
            # 查找该设备最近未结束的报警
            active_alarm = db.query(EFAlarm).filter(
                EFAlarm.FenceName == device_id,
                EFAlarm.EndTime == None
            ).order_by(EFAlarm.StartTime.desc()).first()
            
            if active_alarm:
                active_alarm.EndTime = datetime.utcnow()
                active_alarm.FenceState = "已结束"
                # 如果位置为空，更新位置信息
                if not active_alarm.FenceLocation:
                    active_alarm.FenceLocation = fence_location
                db.commit()
                logger.info(f"结束电子围栏报警: {device_id}, 位置: {fence_location}")
                
    except Exception as e:
        logger.error(f"处理电子围栏报警消息错误: {str(e)}")
    finally:
        db.close()

# 初始化MQTT订阅
@router.on_event("startup")
def init_efence_mqtt():
    client = get_mqtt_client()
    if client:
        client.subscribe("efence/alarm")
        client.message_callback_add("efence/alarm", on_efence_message)
        logger.info("已订阅电子围栏报警主题: efence/alarm")

# 持续时间格式化过滤器
def format_duration(seconds):
    """将秒数格式化为 小时:分钟:秒 格式"""
    if seconds is None:
        return ""   
    hours = seconds // 3600
    minutes = (seconds % 3600) // 60
    seconds = seconds % 60
    return f"{int(hours)}小时{int(minutes)}分钟{int(seconds)}秒"

# 注册过滤器到模板环境
templates.env.filters["format_duration"] = format_duration

# 获取电子围栏报警列表
@router.get("/efence/alerts", response_class=HTMLResponse)
async def get_efence_alerts(
    request: Request,  # 添加 Request 参数
    SessionId: Optional[str] = Cookie(None),
    UserName: Optional[str] = Cookie(None),
    page: int = Query(1, gt=0),
    page_size: int = Query(10, gt=0),
    start_date: str = Query(None),
    end_date: str = Query(None),
    device_id: str = Query(None),
    db: Session = Depends(get_db)
):
    if not SessionId or not UserName or not validate_session(SessionId, UserName):
        return RedirectResponse(url="/")
    query = db.query(EFAlarm).filter(EFAlarm.DeadTime == None)

    # print(f"模板过滤器: {list(templates.env.filters.keys())}")
    
    # 添加时间筛选
    if start_date:
        start_dt = datetime.strptime(start_date, "%Y-%m-%d")
        query = query.filter(EFAlarm.StartTime >= start_dt)
    if end_date:
        end_dt = datetime.strptime(end_date, "%Y-%m-%d") + timedelta(days=1)
        query = query.filter(EFAlarm.StartTime < end_dt)
    
    # 添加设备筛选
    if device_id:
        query = query.filter(EFAlarm.FenceName == device_id)
    
    # 获取设备列表用于下拉框 - 改为从电子围栏设备表获取
    elect_fences = db.query(ElectFence.FenceName).filter(ElectFence.DeadTime == None).distinct().all()
    device_list = [d[0] for d in elect_fences]
    
    # 分页查询
    total = query.count()
    alarms = query.order_by(EFAlarm.StartTime.desc()).offset((page - 1) * page_size).limit(page_size).all()
    
    # 计算持续时间（秒数）
    for alarm in alarms:
        if alarm.EndTime:
            alarm.duration = (alarm.EndTime - alarm.StartTime).total_seconds()
        else:
            alarm.duration = (datetime.utcnow() - alarm.StartTime).total_seconds()

    total_pages = (total + page_size - 1) // page_size  # 计算总页数    

    # 渲染HTML
    return templates.TemplateResponse(
        "efence_alarm.html",
        {
            "request": request,  # 必须包含 request
            "alarms": alarms,
            "page": page,
            "page_size": page_size,
            "total": total,
            "total_pages": total_pages,
            "start_date": start_date or "",
            "end_date": end_date or "",
            "device_id": device_id or "",
            "device_list": device_list
        }
    )


# 删除电子围栏报警
@router.post("/efence/alerts/delete")
async def delete_efence_alerts(
    alarm_ids: list[int],
    db: Session = Depends(get_db)
):
    try:
        current_time = datetime.utcnow()
        for alarm_id in alarm_ids:
            alarm = db.query(EFAlarm).filter(EFAlarm.EFAlarmID == alarm_id).first()
            if alarm:
                alarm.DeadTime = current_time
        db.commit()
        return {"status": "success", "message": f"已删除 {len(alarm_ids)} 条报警记录"}
    except Exception as e:
        db.rollback()
        raise HTTPException(status_code=500, detail=f"删除失败: {str(e)}")
    
@router.get("/analysis/result")
async def analysis_result(
        request: Request,
        # 区域风险分页参数
        page_region: int = Query(1, ge=1, description="区域风险页码"),
        page_size_region: int = Query(10, ge=1, le=100, description="区域风险每页数量"),
        # 小时风险分页参数
        page_hourly: int = Query(1, ge=1, description="小时风险页码"),
        page_size_hourly: int = Query(5, ge=1, le=50, description="小时风险每页数量"),
        db: Session = Depends(get_db)
):
    """风险分析结果页面 - 支持双分页"""

    # 获取区域风险等级数据（分页）
    region_risk_query = """
    SELECT r1.* FROM region_risk_level r1
    INNER JOIN (
        SELECT location, MAX(update_time) as max_time 
        FROM region_risk_level 
        GROUP BY location
    ) r2 ON r1.location = r2.location AND r1.update_time = r2.max_time
    ORDER BY 
        CASE risk_level 
            WHEN '超高风险' THEN 1
            WHEN '高风险' THEN 2
            WHEN '中风险' THEN 3
            WHEN '低风险' THEN 4
            ELSE 5
        END,
        risk_score DESC
    """

    # 获取小时风险分布数据（分页）
    hourly_risk_query = """
    SELECT h1.* FROM hourly_risk_distribution h1
    INNER JOIN (
        SELECT location, MAX(update_time) as max_time 
        FROM hourly_risk_distribution 
        GROUP BY location
    ) h2 ON h1.location = h2.location AND h1.update_time = h2.max_time
    ORDER BY location
    """

    try:
        # 执行查询
        region_risk_df = pd.read_sql(region_risk_query, db.bind)
        hourly_risk_df = pd.read_sql(hourly_risk_query, db.bind)

        # 区域风险分页处理
        total_regions = len(region_risk_df)
        total_pages_region = (total_regions + page_size_region - 1) // page_size_region
        start_idx_region = (page_region - 1) * page_size_region
        end_idx_region = start_idx_region + page_size_region

        paginated_region_risk = region_risk_df.iloc[start_idx_region:end_idx_region]

        # 小时风险分页处理
        total_hourly = len(hourly_risk_df)
        total_pages_hourly = (total_hourly + page_size_hourly - 1) // page_size_hourly
        start_idx_hourly = (page_hourly - 1) * page_size_hourly
        end_idx_hourly = start_idx_hourly + page_size_hourly

        paginated_hourly_risk = hourly_risk_df.iloc[start_idx_hourly:end_idx_hourly]

        # 转换为字典列表供模板使用
        region_risk_data = paginated_region_risk.to_dict('records')
        hourly_risk_data = paginated_hourly_risk.to_dict('records')

        # 统计风险等级分布
        risk_stats = {
            '超高风险': len(region_risk_df[region_risk_df['risk_level'] == '超高风险']),
            '高风险': len(region_risk_df[region_risk_df['risk_level'] == '高风险']),
            '中风险': len(region_risk_df[region_risk_df['risk_level'] == '中风险']),
            '低风险': len(region_risk_df[region_risk_df['risk_level'] == '低风险']),
            '无风险': len(region_risk_df[region_risk_df['risk_level'] == '无风险'])
        }

    except Exception as e:
        print(f"查询数据库错误: {e}")
        region_risk_data = []
        hourly_risk_data = []
        risk_stats = {}
        total_regions = 0
        total_hourly = 0
        total_pages_region = 1
        total_pages_hourly = 1

    return templates.TemplateResponse("analysis_result.html", {
        "request": request,
        "region_risks": region_risk_data,
        "hourly_risks": hourly_risk_data,
        "risk_stats": risk_stats,

        # 区域风险分页参数
        "page_region": page_region,
        "page_size_region": page_size_region,
        "total_regions": total_regions,
        "total_pages_region": total_pages_region,

        # 小时风险分页参数
        "page_hourly": page_hourly,
        "page_size_hourly": page_size_hourly,
        "total_hourly": total_hourly,
        "total_pages_hourly": total_pages_hourly
    })